Database management systems (DBMSs) manage data stored in databases and enable users to formulate queries over the data, returning the corresponding results. For example, a query could be, “give me the names of employees working on projects in the oil and gas industry”. DBMSs include or use optimizers to receive queries (or compiled versions of queries) and produce query execution plans (also termed query plans or execution plans). An execution plan is a tree, set or list of operators (e.g., logic operators) or other commands that instructs another unit, such as an executor, how to execute the query: for example which specific operators to apply and in which order, and how to access the data. Optimizers typically produce execution plans estimated by the optimizer to be optimal in execution cost. Cost may be determined based on processor (CPU) usage, input/output (I/O) cost, or other resource usage, and may be a combined measure of various costs.
Execution plans for the same query may differ when applied to or based on databases having different structures and sizes, different optimizers, or differences between DBMSs, or other differences. Thus if for a given DBMS or data associated with a DBMS a change in its characteristics or attributes occurs (e.g., a change in the structure or indexing of the data, the physical layout or storage of the data, the amount of the data, or the optimizer), the execution plan for a query may change. Various characteristics of a DBMS or data associated with a DBMS may change. Data may change if, e.g., sanitized (e.g., where sensitive information is made safe for wider visibility) or desensitized, if new rows are inserted, or the amount of data changes. An optimizer may change how it constructs a plan or how it relies on the distribution of data. The physical design of the data may change in that the indexes to the data may change, or the partitioning of data may change.
The results of changes in the attributes of the DBMS may include changes, in the resulting query plan, of cardinality and cost estimations (which may be included as part of a query plan), operator choices, operator ordering or arrangement, or other differences.
DBMSs typically make the execution plans available to the users in a human readable or machine-readable form. A DBMS may include a user interface, front end or utility that depicts the plan in a graphical or hierarchical manner, e.g. as a list or operator tree. Some provide different panels with different information about the plan. For example, one DMBS displays the operator tree in one panel and, separately, statistics and cost estimates for each operator, row or line in another panel. Others display a graph (e.g., a flow) of operators. A user may indicate (e.g., by clicking using a pointing device) an operator, and a display (e.g. a window) may expand and contain cost estimates for that operator.
It is common for users (e.g., database administrators and optimizer developers) to compare the plans before and after a change in attributes of a DBMS (e.g., in the optimizer or the data) to analyze the effect of the change in the plan and performance.
Such evaluation is difficult using current query plan viewing tools, as a query plan may be complex and lengthy, and may include much detailed information such as operators that will be applied to execute the query, estimated cardinalities (amount of results, e.g., rows or tuples estimated to be produced for an operator) for the intermediate results and estimated costs of executing each operator. Analyzing this information manually using current tools is cumbersome and time consuming.
Reference numerals may be repeated among the drawings to indicate corresponding or analogous elements. Moreover, some of the blocks depicted in the drawings may be combined into a single function.